from datetime import datetime
from typing import List, Optional, Dict, Any
from flask import json
from sqlalchemy import or_, and_
from sqlalchemy.orm import Session

from src.database.dao.base_dao import BaseDAO
from src.database.manager import DBManager
from src.database.models import UploadedFile
from src.utils.logger import log_info, log_error

class UploadedFileDAO(BaseDAO):
    """用于处理UploadedFile的数据访问对象，整合原AudioUploadDAO功能"""
    
    def __init__(self, db: Session = None):
        self.db = db if db else DBManager().get_session()

    def create(self, 
                    file_path: str, 
                    filename: str,
                    file_type: str,
                    db: Session = None, 
                    group_id: Optional[str] = None,
                    order: int = 0,
                    subtitle_path: Optional[str] = None,
                    uploaded_by: Optional[str] = None,
                    meta_data: Optional[dict] = None, 
                    status: str = 'active',
                    to_dict: bool = False
                    ) -> UploadedFile:
                """创建新的文件记录"""
                
                # 确保有活跃的会话
                close_session = False
                if db is None:
                    db = self.db
                    close_session = True
                
                try:
                    # 准备meta_data字段
                    file_meta = meta_data or {}
                    
                    # 将AudioUpload特有字段加入meta_data
                    if group_id:
                        file_meta['group_id'] = group_id
                    if order:
                        file_meta['order'] = order
                    if subtitle_path:
                        file_meta['subtitle_path'] = subtitle_path
                    
                    # 创建对象
                    file_upload = UploadedFile(
                        file_path=file_path,
                        filename=filename,
                        file_type=file_type,
                        uploaded_by=uploaded_by,
                        meta_data=file_meta,
                        status=status
                    )
                    
                    db.add(file_upload)
                    db.flush()  # 刷新以获取生成的ID
                    
                    # 只有在我们创建了会话时才提交
                    if close_session:
                        db.commit()
                        
                    if to_dict:
                        return self.to_dict(file_upload)
                    return file_upload
                    
                except Exception as e:
                    # 回滚事务
                    db.rollback()
                    log_error(f"创建文件记录失败: {e}")
                    raise
                    
                finally:
                    # 如果我们创建了会话，则负责关闭它
                    if close_session and db:
                        db.close()

    def create_audio_with_subtitle(self, 
                                   audio_path: str, 
                                   audio_filename: str,
                                   subtitle_path: Optional[str] = None,
                                   subtitle_filename: Optional[str] = None,
                                   group_id: Optional[str] = None,
                                   order: int = 0,
                                   uploaded_by: Optional[str] = None,
                                   meta_data: Optional[dict] = None,
                                   status: str = 'active') -> Dict:
        """创建音频文件记录，如果提供了字幕则一并创建"""
        db = self.db
        try:
            # 开始事务
            audio_meta = json.loads(meta_data) if meta_data else {}
            # 合并其他元数据
            if group_id:
                audio_meta['group_id'] = group_id
            if order:
                audio_meta['order'] = order
            if subtitle_path:
                audio_meta['subtitle_path'] = subtitle_path
            if subtitle_filename:
                audio_meta['subtitle_filename'] = subtitle_filename
            
            # 创建音频记录
            audio_file = self.create(
                file_path=audio_path,
                filename=audio_filename,
                file_type='audio',
                db=db,
                uploaded_by=uploaded_by,
                meta_data=audio_meta,
                status=status
            )
            
            db.commit()
            
            return {
                "success": True,
                "audio_file": self.to_dict(audio_file)
            }
            
        except Exception as e:
            db.rollback()
            error_message = f"创建音频和字幕记录失败: {e}"
            log_error(error_message)
            return {"success": False, "message": error_message}
    
    def get_by_id(self, file_id: int, to_dict: bool=False) -> Optional[UploadedFile]:
        """通过ID获取文件记录"""
        first = self.db.query(UploadedFile).filter(UploadedFile.id == file_id).first()
        if to_dict:
            return self.to_dict(first)
        return first
    
    def get_audio_by_id(self, audio_id: int) -> Optional[Dict]:
        """通过音频ID获取音频文件记录"""
        result = self.db.query(UploadedFile).filter(
            UploadedFile.id == audio_id,
            UploadedFile.file_type == 'audio'
        ).first()
        if result:
            result.audio_path = result.file_path
            if result.meta_data:
                meta_data = result.meta_data
                result.subtitle_path = meta_data.get('subtitle_path')
                result.group_id = meta_data.get('group_id')
                result.order = meta_data.get('order', 0)
        return result
    
    def get_by_group_id(self, group_id: str, file_type: Optional[str] = None) -> List[Dict]:
        """获取指定分组的所有文件"""
        query = self.db.query(UploadedFile).filter(
            UploadedFile.meta_data['group_id'].astext == group_id
        )
        
        if file_type:
            query = query.filter(UploadedFile.file_type == file_type)
        
        # 按元数据中的order排序
        files = query.all()
        
        # 手动排序（因为不同数据库对JSON排序支持不同）
        files_with_order = [(f, f.meta_data.get('order', 0) if f.meta_data else 0) for f in files]
        sorted_files = sorted(files_with_order, key=lambda x: x[1])
        results = self.to_dict_list([f[0] for f in sorted_files])
        results = self._audio_handle_meta(results)
        return results
    
    def get_by_type(self, file_type: str, page: int = 1, page_size: int = 10) -> Dict:
        """获取指定类型的所有文件，分页返回"""
        try:
            # 计算分页偏移量
            offset = (page - 1) * page_size
            
            # 查询总记录数
            total_count = self.db.query(UploadedFile).filter(
                UploadedFile.file_type == file_type
            ).count()
            
            # 查询分页数据
            query = self.db.query(UploadedFile).filter(
                UploadedFile.file_type == file_type
            )
            query = query.order_by(UploadedFile.created_at.desc())
            query = query.offset(offset).limit(page_size)
            records = query.all()
            
            # 计算总页数
            total_pages = (total_count + page_size - 1) // page_size
            
            return {
                "success": True,
                "data": {
                    "records": self.to_dict_list(records),
                    "pagination": {
                        "page": page,
                        "page_size": page_size,
                        "total_count": total_count,
                        "total_pages": total_pages
                    }
                }
            }
        except Exception as e:
            log_error(f"查询{file_type}文件记录失败: {e}")
            return {
                "success": False,
                "message": f"查询失败: {e}"
            }
    
    def get_audio_files(self, page: int = 1, page_size: int = 10) -> Dict:
        """获取所有音频文件，分页返回（替代原get_audio_history方法）"""
        results = self.get_by_type('audio', page, page_size)
        records = results.get("data", {}).get("records", [])
        records = self._audio_handle_meta(records)
        results["data"]["records"] = records
        return results
    
    def _audio_handle_meta(self, records) -> Dict:
        """处理音频文件的元数据"""
        for record in records:
            # 确保音频路径和元数据正确
            record['audio_path'] = record.get('file_path')
            if record.get('meta_data'):
                meta_data = record['meta_data']
                record['subtitle_path'] = meta_data.get('subtitle_path')
                record['group_id'] = meta_data.get('group_id')
                record['order'] = meta_data.get('order', 0)
        return records
    def get_audio_with_subtitle(self, audio_id: int) -> Dict:
        """获取音频文件和关联的字幕文件"""
        audio_file = self.get_by_id(audio_id)
        if not audio_file or audio_file.file_type != 'audio':
            return {"success": False, "message": "指定的音频ID不存在或不是音频文件"}
        
        # 获取字幕路径
        subtitle_path = audio_file.meta_data.get('subtitle_path') if audio_file.meta_data else None
        subtitle_file = None
        
        # 如果有字幕路径，尝试查找字幕记录
        if subtitle_path:
            subtitle = self.db.query(UploadedFile).filter(
                UploadedFile.file_path == subtitle_path,
                UploadedFile.file_type == 'subtitle'
            ).first()
            
            if subtitle:
                subtitle_file = self.to_dict(subtitle)
        
        return {
            "success": True,
            "audio_file": self.to_dict(audio_file),
            "subtitle_file": subtitle_file
        }
    
    def update_status(self, file_id: int, status: str) -> bool:
        """更新文件状态"""
        try:
            file = self.get_by_id(file_id)
            if not file:
                log_error(f"更新文件状态失败: ID {file_id} 不存在")
                return False
            
            file.status = status
            self.db.commit()
            return True
        except Exception as e:
            self.db.rollback()
            log_error(f"更新文件状态失败: {e}")
            return False
    
    def delete(self, file_id: int) -> bool:
        """删除文件记录"""
        try:
            file = self.get_by_id(file_id)
            if not file:
                return False
            
            self.db.delete(file)
            self.db.commit()
            return True
        except Exception as e:
            self.db.rollback()
            log_error(f"删除文件记录失败: {e}")
            return False
        
    def create_video(self, 
                        video_path: str, 
                        video_filename: str,
                        video_type: str, 
                        meta_data: str,
                        status: str = 'active') -> Dict:
        """创建视频文件记录"""
        meta_data = json.loads(meta_data)
        if video_type:
            meta_data['video_type'] = video_type
        return self.create(
            file_path=video_path,
            filename=video_filename,
            file_type="video",
            meta_data=meta_data,
            status=status
        )
        
    def set_default_video(self, video_id: int, video_type: str) -> Dict:
        """设置指定视频为默认视频"""
        try:
            # Reset is_default for all videos
            videos = self.db.query(UploadedFile).filter(
                UploadedFile.file_type == 'video',
                UploadedFile.meta_data['video_type'].astext == video_type,
            ).all()
            
            for video in videos:
                meta = video.meta_data.copy() or {}
                if video.id == video_id:
                    meta['is_default'] = True
                else:
                    meta['is_default'] = False
                video.meta_data = meta

            self.db.flush()
            self.db.commit()
            
            return {
                "success": True,
                "message": "视频已设置为默认",
                "video": self.to_dict(video)
            }
        except Exception as e:
            self.db.rollback()
            log_error(f"设置默认视频失败: {e}")
            return {"success": False, "message": str(e)}
        
    def get_intro_outro_videos(self, video_type=None):
        """
        获取片头片尾视频列表
        
        :param video_type: 可选，视频类型 'opening' 或 'ending'
        :return: 符合条件的视频列表
        """
        try:
            query = self.db.query(UploadedFile).filter(UploadedFile.status == 'active')
            
            if video_type:
                # 加入查询条件，过滤视频类型
                query = query.filter(UploadedFile.meta_data["video_type"].astext == video_type)
                
            videos = []
            for file in query:
                # 忽略没有video_type的记录
                if not file.meta_data or 'video_type' not in file.meta_data:
                    continue
                    
                # 只添加片头或片尾类型的视频
                if file.meta_data['video_type'] in ['opening', 'ending', 'mid']:
                    videos.append({
                        "id": file.id,
                        "filename": file.filename,
                        "file_path": file.file_path,
                        "is_default": file.meta_data.get('is_default', False),
                        "video_type": file.meta_data['video_type'],
                        "created_at": file.created_at.strftime("%Y-%m-%d %H:%M:%S") if file.created_at else None
                    })
                    
            return {"success": True, "videos": videos}
        except Exception as e:
            return {"success": False, "message": str(e)}
        
    def create_subtitle_with_unique_path(self, 
                        subtitle_path: str, 
                        subtitle_filename: str,
                        uploaded_by: Optional[str] = None,
                        meta_data: Optional[dict] = None, 
                        status: str = 'active') -> Dict:
        """创建字幕文件记录"""
        try:
            # 准备meta_data字段
            subtitle_meta = meta_data.copy() if meta_data else {}
            
            self.db.query(UploadedFile).filter(
                UploadedFile.file_type == 'subtitle',
                UploadedFile.file_path == subtitle_path
            ).delete(synchronize_session=False)
            
            subtitle_file = self.create(
                file_path=subtitle_path,
                filename=subtitle_filename,
                file_type='subtitle',
                uploaded_by=uploaded_by,
                meta_data=subtitle_meta,
                status=status
            )
            
            return {
                "success": True,
                "subtitle_file": self.to_dict(subtitle_file)
            }
        except Exception as e:
            log_error(f"创建字幕文件记录失败: {e}")
            return {"success": False, "message": str(e)}
        
    def create_football_icon(self, 
                                    icon_path: str, 
                                    icon_filename: str,
                                    icon_type: str,
                                    uploaded_by: Optional[str] = None,
                                    meta_data: Optional[dict] = None, 
                                    status: str = 'active') -> Dict:
        """创建足球图标文件记录"""
        try:
            # 准备meta_data字段
            icon_meta = meta_data.copy() if meta_data else {}
            if icon_type:
                icon_meta['icon_type'] = icon_type
            
            icon_file = self.create(
                file_path=icon_path,
                filename=icon_filename,
                file_type='football_icon',
                uploaded_by=uploaded_by,
                meta_data=icon_meta,
                status=status,
                db=self.db,
            )
            
            return {
                "success": True,
                "icon_file": self.to_dict(icon_file)
            }
        except Exception as e:
            log_error(f"创建足球图标文件记录失败: {e}")
            return {"success": False, "message": str(e)}
        
    def update_football_icon_filename(self, 
                                        icon_id: int, 
                                        icon_filename: str) -> Dict:
        """更新足球图标文件名"""
        try:
            icon_file = self.get_by_id(icon_id)
            duplicate = self.db.query(UploadedFile).filter(
                UploadedFile.id != icon_id,
                UploadedFile.file_type == 'football_icon',
                UploadedFile.filename == icon_filename            
            ).count()
            if duplicate > 0:
                raise ValueError("图标文件名已存在，请使用其他名称")
            if not icon_file or icon_file.file_type != 'football_icon':
                raise ValueError("指定的图标ID不存在或不是足球图标文件")
            
            # 更新文件名
            icon_file.filename = icon_filename
            self.db.commit()
            return icon_file.filename

        except Exception as e:
            raise Exception(f"更新足球图标文件名失败: {e}")
        
    def get_football_icons(self, icon_type, 
                            page: int = 1, 
                            page_size: int = 10) -> Dict:
        """获取指定类型的足球图标，分页返回"""
        try:
            # 计算分页偏移量
            offset = (page - 1) * page_size
            
            # 查询总记录数
            total_count = self.db.query(UploadedFile).filter(
                UploadedFile.file_type == 'football_icon',
                UploadedFile.meta_data['icon_type'].astext == icon_type
            ).count()
            
            # 查询分页数据
            query = self.db.query(UploadedFile).filter(
                UploadedFile.file_type == 'football_icon',
                UploadedFile.meta_data['icon_type'].astext == icon_type
            )
            query = query.order_by(UploadedFile.created_at.desc())
            query = query.offset(offset).limit(page_size)
            records = self.to_dict_list(query.all())
            
            return records, total_count
        except Exception as e:
            raise Exception(f"查询足球图标记录失败: {e}")
            
    def delete_football_icon(self, icon_id: int) -> Optional[UploadedFile]:
        """删除足球图标记录"""
        try:
            icon_file = self.get_by_id(icon_id)
            if not icon_file or icon_file.file_type != 'football_icon':
                raise Exception("指定的图标ID不存在或不是足球图标文件")
            
            self.db.delete(icon_file)
            self.db.commit()
            return icon_file
        except Exception as e:
            self.db.rollback()
            raise Exception(f"删除足球图标记录失败: {e}")
        
        
    def update_subtitle_videos_mappings(self, mappings: List[Dict[str, Any]]) -> int:
        """更新字幕与视频的映射关系"""
        try:
            updated_count = 0
            for mapping in mappings:
                subtitle_path = mapping.get('subtitle_path')
                if not subtitle_path:
                    continue
                
                video_paths = mapping.get('video_paths')
                icon_video_paths = mapping.get('icon_video_paths')
                updated_at = mapping.get('updated_at')
                # 找到字幕记录
                subtitle = self.db.query(UploadedFile).filter(
                    UploadedFile.file_type == 'subtitle',
                    UploadedFile.file_path == subtitle_path
                ).first()
                
                log_info(subtitle)
                if subtitle:
                    # 更新meta_data字段
                    meta_data = subtitle.meta_data.copy() or {}
                    if video_paths:
                        meta_data['video_paths'] = video_paths
                    if updated_at:
                        meta_data['updated_at'] = updated_at 
                    if icon_video_paths:
                        meta_data['icon_video_paths'] = icon_video_paths 
                    subtitle.meta_data = meta_data
                    updated_count += 1
            
            self.db.commit()
            return updated_count
        except Exception as e:
            self.db.rollback()
            raise Exception(f"更新字幕与视频映射关系失败: {e}")
        
    def get_subtitle_list(self, 
                          page: int = 1, 
                          page_size: int = 10, 
                          query: str = '') -> Dict:
        """获取字幕列表，支持分页和查询"""
        try:
            # 计算分页偏移量
            offset = (page - 1) * page_size
            
            # 查询总记录数
            total_count = self.db.query(UploadedFile).filter(
                UploadedFile.file_type == 'subtitle',
                or_(
                    UploadedFile.filename.ilike(f'%{query}%'),
                    UploadedFile.file_path.ilike(f'%{query}%')
                )
            ).count()
            
            # 查询分页数据
            query = self.db.query(UploadedFile).filter(
                UploadedFile.file_type == 'subtitle',
                or_(
                    UploadedFile.filename.ilike(f'%{query}%'),
                    UploadedFile.file_path.ilike(f'%{query}%')
                )
            ).order_by(UploadedFile.meta_data['updated_at']
                       .astext.desc()).order_by(UploadedFile.created_at.desc())
            query = query.offset(offset).limit(page_size)
            records = self.to_dict_list(query.all())
            
            return records, total_count
        except Exception as e:
            log_error(f"获取字幕列表失败: {e}")
            raise Exception(f"获取字幕列表失败: {e}")
